﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;using System.Configuration;
using System.Windows.Forms;

namespace ChairtyBank.DAL
{
    class BackupEntity
    {
        public Int32 Id { get; set; }
        public DateTime DueDate { get; set; }
        public Int32 Amount { get; set; }
        public Int32 Penalty { get; set; }
        public Int32 Total { get; set; }
        public DateTime DateOfPay { get; set; }
        public String Notes { get; set; }     
        public Int32 BillNum { get; set; }
        public Int32 Loan_ID { get; set; }
        public Int32 Payed_Amount { get; set; }
        public bool payed { get; set; }
        public Int32 Rest { get; set; }
        public Int32 Customer_ID { get; set; }
        public String CustomerName { get; set; }
        public String ProjectName { get; set; }


    }

    /*
 
        Class Client which contains all methods related to Customer table
 
    */


    class Backup
    {
        //Set absolute DB path rather than relative to solve openFileDialog reset path problem
        private static string conString = ConfigurationManager.ConnectionStrings["ChairtyBank"].ToString();
        private static string cmdText = "";





        public static List<BackupEntity> GetInstalment(int Loan_ID)
        {
            List<BackupEntity>  IList = new List<BackupEntity>();


            cmdText = "select * from Backup where Loan_ID=@Loan_ID";
            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);
            cmd.Parameters.AddWithValue("@Loan_ID", Loan_ID);
            try
            {
                con.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                while(reader.Read())
                {

                    IList.Add(fillEntityFromReader(reader));
                }
          //      MessageBox.Show("Get"+IList.Count+ "Records");
                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception==>>" + ex.Message);
            }

            finally
            {
                if(con.State == ConnectionState.Open)
                    con.Close();
            }

            return IList;
        }


        public static BackupEntity GetInstalmentByInsId(int Instalment_ID)
        {
            BackupEntity I = new BackupEntity();


            cmdText = "select * from Backup where Instalment_ID=@Instalm_ID";
            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);
            cmd.Parameters.AddWithValue("@Instalm_ID", Instalment_ID);
            try
            {
                con.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {

                    I=fillEntityFromReader(reader);
                }
                //      MessageBox.Show("Get"+IList.Count+ "Records");
                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception==>>" + ex.Message);
            }

            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return I;
        }  
     

        public static List<BackupEntity> GetAllInstalment()
        {
            List<BackupEntity> IList = new List<BackupEntity>();

            cmdText = "select * from Backup ";
            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);

            try
            {
                con.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                   IList.Add(fillEntityFromReader(reader));
                }
                reader.Close();
              
            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception==>>" + ex.Message);
            }

            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
              
            }

            return IList;

        }




        public static void Add(InstalmentEntity I, Int32 Customer_ID ,String CustName , String ProjName)
        {
            cmdText = "insert into Backup (Instalment_DueDate, Instalment_Amount, Instalment_Penalty ,Instalment_Total ,Instalment_DateOfPay ,Instalment_Notes,Customer_ID, Loan_ID,Payed,Payed_Amount,Rest,Customer_Name,Project_Name)values"+
                "(@Instalment_DueDate,@Instalment_Amount,@Instalment_Penalty,@Instalment_Total,@Instalment_DateOfPay,@Instalment_Notes,@Bill_Num,@Loan_ID,@Payed,@payed_Amount,@Rest,@Customer_Name,@Project_Name)";
              
            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);
            cmd.Parameters.AddWithValue("@Instalment_DueDate", I.DueDate);
            cmd.Parameters.AddWithValue("@Instalment_Amount", I.Amount);
            cmd.Parameters.AddWithValue("@Instalment_Penalty", I.Penalty);
            cmd.Parameters.AddWithValue("@Instalment_Total", I.Total);
            cmd.Parameters.AddWithValue("@Instalment_DateOfPay", I.DateOfPay);
            cmd.Parameters.AddWithValue("@Instalment_Notes", I.Notes);
            cmd.Parameters.AddWithValue("@Bill_Num", Customer_ID);
            cmd.Parameters.AddWithValue("@Loan_ID", I.Loan_ID);
            cmd.Parameters.AddWithValue("@Payed", I.payed);
            cmd.Parameters.AddWithValue("@payed_Amount", I.Payed_Amount);
            cmd.Parameters.AddWithValue("@Rest", I.Rest);
            cmd.Parameters.AddWithValue("@Customer_Name", CustName);
            cmd.Parameters.AddWithValue("@Project_Name", ProjName);
           
        
            try
            {
                con.Open();

                Int32 numOfRow = cmd.ExecuteNonQuery();
              
               
            }
            catch(Exception ex){
                MessageBox.Show("Exception==>>"+ex.Message);
            }
            finally
            {
                if(con.State == ConnectionState.Open)
                    con.Close();
            }

            

        }



        /*

      public static bool Update(BackupEntity I)
      {
          Boolean flag = false;
          cmdText = "UPDATE Backup SET Instalment_DueDate=? , Instalment_Amount=? , Instalment_Penalty=? ,Instalment_Total=? ,Instalment_DateOfPay=? ,Instalment_Notes=? ,Bill_Num=?, Loan_ID=? ,Payed=?, Payed_Amount=?, Rest=?  WHERE Instalment_ID=? ";
          SqlConnection con = new SqlConnection(conString);
          SqlCommand cmd = new SqlCommand(cmdText, con);

          cmd.Parameters.AddWithValue("@Instalment_DueDate", I.DueDate);
          cmd.Parameters.AddWithValue("@Instalment_Amount", I.Amount);
          cmd.Parameters.AddWithValue("@Instalment_Penalty", I.Penalty);
          cmd.Parameters.AddWithValue("@Instalment_Total", I.Total);
          cmd.Parameters.AddWithValue("@Instalment_DateOfPay", I.DateOfPay);
          cmd.Parameters.AddWithValue("@Instalment_Notes", I.Notes);
          cmd.Parameters.AddWithValue("@Bill_Num", I.BillNum);
          cmd.Parameters.AddWithValue("@Loan_ID", I.Loan_ID);
          cmd.Parameters.AddWithValue("@Payed", I.payed);
          cmd.Parameters.AddWithValue("@payed_Amount", I.Payed_Amount);
          cmd.Parameters.AddWithValue("@Rest", I.Rest);
          cmd.Parameters.AddWithValue("@Loan_ID", I.Id);

        
          try
          {
              con.Open();

              Int32 numOfRow = cmd.ExecuteNonQuery();
              MessageBox.Show(numOfRow + "Record updated");
              flag = true;
          }
          catch (Exception ex)
          {
              MessageBox.Show("Exception==>>" + ex.Message);
          }
          finally
          {
              if (con.State == ConnectionState.Open)
                  con.Close();
          }

          return flag;
      }


           
              Delete using id or Serial 
           */

        public static bool Delete(BackupEntity I)
        {
            Boolean flag = false;
            cmdText = "Delete from Backup WHERE Instalment_ID=@Loan_ID";

                SqlConnection con = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand(cmdText, con);
                cmd.Parameters.AddWithValue("@Loan_ID", I.Id);
                try
                {
                    con.Open();

                    Int32 numOfRow = cmd.ExecuteNonQuery();
                    flag = true;


                }
                catch (Exception ex)
                {
                    MessageBox.Show("Exception==>>" + ex.Message);
                }
                finally
                {
                    if (con.State == ConnectionState.Open)
                        con.Close();
                }
            
            return flag;
        }





   




        private static BackupEntity fillEntityFromReader(SqlDataReader reader)
        {
         

            BackupEntity I = new BackupEntity();

            I.Id = Int32.Parse(reader["Instalment_ID"].ToString());

            if (reader["Loan_ID"] != null)
                I.Loan_ID = Int32.Parse(reader["Loan_ID"].ToString());
            if (reader["Instalment_Amount"] != null)
                I.Amount = Int32.Parse(reader["Instalment_Amount"].ToString());
            if (reader["Instalment_Penalty"] != null)
                I.Penalty = Int32.Parse(reader["Instalment_Penalty"].ToString());
            if (reader["Instalment_DueDate"] != null)
                I.DueDate = (DateTime)reader["Instalment_DueDate"];
            if (reader["Instalment_DateOfPay"] != null)
                I.DateOfPay = (DateTime)reader["Instalment_DateOfPay"];
            if (reader["Instalment_Total"] != null)
                I.Total = Int32.Parse(reader["Instalment_Total"].ToString());
            if (reader["Instalment_Notes"] != null)
                I.Notes = reader["Instalment_Notes"].ToString();
            if (reader["Bill_Num"] != null)
                I.BillNum = Int32.Parse(reader["Bill_Num"].ToString());
            I.payed = bool.Parse(reader["Payed"].ToString());
            I.Payed_Amount = Int32.Parse(reader["Payed_Amount"].ToString());
            I.Rest = Int32.Parse(reader["Rest"].ToString());
            I.CustomerName = reader["Customer_Name"].ToString();
            I.ProjectName = reader["Project_Name"].ToString();
            return I;
        }

         
    }
}
